# -*- coding: utf-8 -*-

# Copyright (c) 2011, Sergey Karakovskiy, Nikolay Sohryakov
# All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions are met:
#     * Redistributions of source code must retain the above copyright
#       notice, this list of conditions and the following disclaimer.
#     * Redistributions in binary form must reproduce the above copyright
#       notice, this list of conditions and the following disclaimer in the
#       documentation and/or other materials provided with the distribution.
#     * Neither the name of Yasker nor the
#       names of its contributors may be used to endorse or promote products
#       derived from this software without specific prior written permission.
#
# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
# THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
# PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS
# BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
# CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
# SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
# INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
# CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
# ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
# POSSIBILITY OF SUCH DAMAGE.
import logging
import sqlite3


__author__ = 'Sergey Karakovskiy, Nikolay Sohryakov'
__copyright__ = 'Copyright 2011, Yasker'
__license__ = 'New BSD'
__email__ = 'Nikolay.Sohryakov+yasker@gmail.com, Sergey.Karakovskiy+yasker@gmail.com'

_db_connection = None
_is_connected = False

def connect(db_file):
	"""
	Connect to the database stored in db_file.

	If succeed returns True, otherwise False.
	"""
	global _db_connection
	global _is_connected
	_db_connection = sqlite3.connect(db_file, check_same_thread=False)
	if not _db_connection:
		logging.critical('Cannot connect to the database')
		_is_connected = False
		return
	_is_connected = True
	return _is_connected


def close():
	"""Close database connection."""
	global _db_connection
	_db_connection.close()


def get_all_projects():
	"""
	   Get a list of all projects
	"""
	query = """
			SELECT * FROM Projects
			"""
	global _db_connection
	return _db_connection.execute(query).fetchall()


def get_root_tasks(p_id):
	"""
	Get root tasks for the project with id == p_id
	"""
	query = """
			SELECT * FROM Tasks WHERE p_id = ? AND t_parent IS NULL
			"""
	global _db_connection
	return _db_connection.execute(query, [p_id]).fetchall()


def get_sub_tasks(t_id):
	"""
	Get sub tasks for the task with the given id
	"""
	query = """
			SELECT * FROM Tasks WHERE t_parent = ?
			"""
	global _db_connection
	return  _db_connection.execute(query, [t_id]).fetchall()

def get_comments(project_id, task_id, milestone_id):
	"""
	   Not implemented yet.
	"""
	pass


def add_project(project_details):
	"""
	Add a new project in to the storage.
	"""
	query = """
			INSERT INTO Projects (p_id, title, description) VALUES (?,?,?)
			"""
	global _db_connection
	print _db_connection.execute(query, project_details)
	_db_connection.commit()


def add_milestone(milestone_details):
	"""
	Add a new milestone to the project in storage.
	"""
	query = """
			INSERT INTO Milestones (p_id, m_id, title, description) VALUES (?,?,?,?)
			"""
	global _db_connection
	_db_connection.execute(query, milestone_details[:-1])
	_db_connection.commit()


def add_task(task):
	"""
	Add a new task to the storage.

	task is a tuple (p_id, milestone_id, task_id, title, note, updated, due_to,
	parent, priority, status)
	"""
	query = """
			INSERT INTO Tasks
				(p_id, t_parent, t_id, title, description) VALUES (?, ?, ?, ?, ?)
			"""
	global _db_connection
	_db_connection.execute(query, task)
	_db_connection.commit()


def add_comment():
	"""
	Not implemented yet.
	"""
	pass


def update_milestone(milestone_details):
	"""Update a milestone."""
	query = """
			UPDATE Milestones
			SET title=?,
				description=?,
				due_to=?,
			WHERE m_id=?
		"""
	global _db_connection
	_db_connection.execute(query, milestone_details)
	_db_connection.commit()


def update_task(task_details):
	"""Update task."""
	query = """
			UPDATE Tasks SET
				parent = ?,
				title = ?,
				description = ?,
				due_to = ?,
				owner = ?,
				assigned_to = ?,
				priority = ?,
				status = ?
			WHERE m_id = ?
			"""
	global _db_connection
	_db_connection.execute(query, task_details)
	_db_connection.commit()


def delete_project(p_id):
	pass


def delete_milestone(m_id):
	"""Delete milestone."""
	query_milestone = """
			DELETE FROM Milestones WHERE m_id = ?
			"""
	global _db_connection
	_db_connection.execute(query_milestone, (m_id,))
	query_tasks = """
			DELETE FROM Tasks WHERE m_id = ?
			"""
	_db_connection.execute(query_tasks, (m_id,))
	_db_connection.commit()


def delete_task(t_id):
	"""Delete task."""
	query = """
			DELETE FROM Tasks WHERE t_id = ?
			"""
	global _db_connection
	_db_connection.execute(query, (t_id,))
	_db_connection.commit()


def is_empty():
	"""
	Check if storage is empty.

	If projects table is empty then we assume that storage is empty.
	"""
	query = """
			SELECT * FROM Projects
			"""
	global _db_connection
	return len(_db_connection.execute(query).fetchall()) == 0


def truncate_table(table_name):
	"""
	   Delete all rows from the given table
	   """
	# TODO: quick fix. Don't use python's string substitution. Injection threat.
	query = "DELETE FROM %s" % table_name
	global _db_connection
	_db_connection.execute(query)
	_db_connection.commit()
